Python连接MySQL数据库

共计 2234 个字符，预计需要花费 6 分钟才能阅读完成。

PyMySQL 是一个纯 Python 实现的 MySQL 客户端库，支持兼容 Python 3，用于代替 MySQLdb。

select 查询数据

import pymysql

# 第一步：建立连接
conn = pymysql.connect(
    host="192.168.2.0",
    port=3306,
    user="test",
    passwd="",
    database="test",
    charset="utf8mb4",
)
try:
    # 第二步：获取游标（字典型游标）with conn.cursor() as cursor:
        # 第三步：执行 SQL，返回受影响行数
        sql = "select * from aaa"
        rows = cursor.execute(sql)
        print(" 受影响行数:", rows)

        # 第四步：通过游标抓取数据
        for row in iter(cursor.fetchall()):
            print(row)
except pymysql.MySQLError as err:
    print(err)
finally:
    # 第五步：关闭连接
    conn.close()

insert 插入数据

import pymysql

## 第一步：建立连接
conn = pymysql.connect(host='', port=3306,
                       user='', passwd='',
                       database='', charset='utf8mb4')
try:
    # 第二步：获取游标
    with conn.cursor() as cursor:
        # 第三步：执行 SQL
        affected_rows = cursor.execute('insert into tb values (%s, %s)',
            (10, '姓名')
        )
        if affected_rows == 1:
            print('插入成功！')

    # 第四步：提交
    conn.commit()
except pymysql.MySQLError as err:
    print(err)
    # 第四步：回滚
    conn.rollback()
finally:
    # 第五步：关闭连接
    conn.close()

update 修改数据

import pymysql

conn = pymysql.connect(host="", port=3306, user="", passwd="", database="", charset="utf8mb4")

sql = "update table set name=%s where id=%s"
data = (" 张三 ", 3)
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()

cursor.close()
conn.close()

delete 删除数据

import pymysql

conn = pymysql.connect(host="", port=3306, user="", passwd="", database="", charset="utf8mb4")

sql = "delete from table where id=%s"
data = (3,)
cursor = conn.cursor()
cursor.execute(sql, data)
conn.commit()

cursor.close()
conn.close()

类写法

import pymysql

# 定义数据库相关配置项
DB_CONFIG = {
    "host": "192.168.2.60",
    "port": 3306,
    "user": "test",
    "password": "xxbcw2019X",
    "database": "test",
    "charset": "utf8",
}

class SQLManager(object):
    def __init__(self):
        self.conn = None
        self.cursor = None
        self.connect()

    def connect(self):
        self.conn = pymysql.connect(host=DB_CONFIG["host"],
            port=DB_CONFIG["port"],
            user=DB_CONFIG["user"],
            password=DB_CONFIG["password"],
            db=DB_CONFIG["database"],
            charset=DB_CONFIG["charset"],
        )
        self.cursor = self.conn.cursor()

    def get_all(self, sql, *args):
        """ 查询数据 """
        self.cursor.execute(sql, *args)
        return self.cursor.fetchall()

    def update_tb(self, sql, *args):
        self.cursor.execute(sql, *args)
        self.conn.commit()

    def insert_into(self, sql, *args):
        self.cursor.execute(sql, *args)
        self.conn.commit()

    def __del__(self):
        """ 关闭游标和数据库连接 """
        self.cursor.close()
        self.conn.close()

if __name__ == "__main__":
    db = SQLManager()
    sql = "select drug,name from test where drug=%s and name=%s order by name"
    results = db.get_all(sql, ("drug", "name"))
    print(results)